﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class HopDongDienDAL : BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @" SELECT * ,b.ten_nv as Ten_NV,c.ten_kh as Ten_KH,d.diengiai as diengiai FROM hopdongdien a join nhanvien b on a.ma_nv=b.ma_nv join khachhang c on a.ma_kh=c.ma_kh join mucdichsudung d on a.ma_md=d.ma_md ";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @" DELETE FROM nhanvien WHERE ma_md = '{0}' ";
            sql = string.Format(sql,id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Hop_Dong_Dien)obj;
            string sql = @"set dateformat mdy; INSERT INTO hopdongdien(ma_hd,ngaylap,ngaycapdien,ma_nv,ma_kh,ma_md,ma_trth) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
            sql = string.Format(sql,o.ma_hd,o.ngaylap,o.ngaycapdien,o.ma_nv,o.ma_kh,o.ma_trth,o.ma_md);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Hop_Dong_Dien)obj;
            string sql = @"set dateformat mdy; UPDATE nhanvien SET ngaylap='{0}',ngaycapdien='{1}',ma_nv='{2}',ma_kh='{3}',ma_md='{4}',ma_trth='{5}' WHERE ma_hd='{6}'";
            sql = string.Format(sql,o.ngaylap, o.ngaycapdien, o.ma_nv, o.ma_kh, o.ma_md,o.ma_trth,o.ma_hd);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_nhanvien()
        {
            string sql = @" SELECT ma_nv as id, ten_nv as ten FROM nhanvien";
            return ExecuteQuery(sql);
        }
        public DataTable Select_khachhang()
        {
            string sql = @" SELECT ma_kh as id, ten_kh as ten FROM khachhang";
            return ExecuteQuery(sql);
        }
        public DataTable Select_mucdichsudung()
        {
            string sql = @" SELECT ma_md as id, diengiai as ten FROM mucdichsudung";
            return ExecuteQuery(sql);
        }

        public DataTable Select_trangthai()
        {
            string sql = @" SELECT ma_trth as id, ten_trth as ten FROM trangthai";
            return ExecuteQuery(sql);
        }
        public string select_max()
        {
            string sql = @"select max(ma_hd) as max from hopdongdien";
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["max"] + "" : "";
        }
        public DataTable select_HDTDReport(string id)
        {
            string sql = @"select a.Ma_HD as Mahopdong,a.NgayLap as Ngaylap,b.CMND,b.DiaChi as Diachi,b.Ten_KH as HotenKH,c.Ten_NV as HotenNV,d.DienGiai as MucdichSD,b.NgaySinh as NgaysinhKH,b.SDT
                        from HOPDONGDIEN a join KHACHHANG b on a.Ma_KH=b.Ma_KH join NHANVIEN c on a.Ma_NV=c.Ma_NV join MUCDICHSUDUNG d on a.Ma_MD=d.Ma_MD
                        where a.Ma_HD='{0}'";

            
            sql = string.Format(sql, id);

            return ExecuteQuery(sql);
        }
    }
}
